# -*- coding: utf-8 -*-
'''
| @author: stone.li
| Created on: 2022/11/21 12:01
| desc: 产业对比页面SQL查询
'''


import logging, datetime, time, requests, traceback, functools
import base.globalvars as glo
from util.db_util import execute_query,get_query_result


def chain_id_transformation(tree_id):
    """产业链ID转化
    """
    sql = "SELECT id from selected_ind_chain WHERE chain_id = %s" % tree_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)


def get_city_rank_info(region_code):
    """查询城市排名
    :param region_code：地区CODE
    :return: 城市排名相关6个城市
    """
    # datetime_year = datetime.date.today().year - 1
    datetime_year = 2021
    # 查询排名表中所有地区的排名
    sql = '''SELECT city_code FROM city_digital_economy_score WHERE u_year = %s ORDER BY  score_ranking ''' % datetime_year
    try:
        res_city = execute_query(sql)
    except Exception as e:
        logging.error("get_city_rank_info--SQL查询失败：%s" % e)
    # 确认接口所传参数region_code的排名
    rank = res_city.index((region_code,))
    # 前2名
    if rank in (0, 1):
        region_codes = [i[0] for i in list(res_city)[0:6]]
    # 后三名
    elif rank >= 240:
        region_codes = [i[0] for i in list(res_city)[-6:]]
    # 正常排名
    else:
        region_codes = [i[0] for i in list(res_city)[rank-2: rank+4]]
    region = [(i, get_city_name(i)) for i in region_codes]
    return region


def get_city_name(region_code):
    sql = '''SELECT name FROM region_dictionary WHERE region_code  = %s''' % region_code
    try:
        city_name = execute_query(sql)
    except Exception as e:
        logging.error("get_city_name--SQL查询失败：%s" % e)
    return city_name[0][0]


def get_com_incremental(region_code, tree_id):
    """查询地区主要企业信息
    :param region_code：地区CODE
    :param tree_id：产业链id
    :return: 地区企业基本信息
    """
    datetime_year = datetime.date.today().year - 1
    chain_his_id = chain_id_transformation(tree_id)
    sql = "SELECT YEAR,ent_add_year ,ent_add_ranking_year ," \
          "ent_count_year ,ent_count_ranking_year ," \
          "financing_amount_year ,financing_amount_ranking_year ,financing_count_year ,financing_count_ranking_year," \
          "leading_ent_count_year,leading_ent_count_ranking_year ,bond_ent_count_year,bond_ent_ranking_year,three_board_ent_count_year,three_board_ent_ranking_year,listed_ent_count_year,listed_ent_ranking_year," \
          "leading_ent_revenue,leading_ent_revenue_ranking,leading_ent_profit,leading_ent_profit_ranking," \
          "soft_count_year,soft_ranking,patent_count_year,patent_ranking,trademark_count_year,trademark_ranking,copyright_count_year,copyright_ranking," \
          "tech_sme_ent_count_year,tech_sme_ent_count_ranking_year,tech_center_ent_count_year,tech_center_ent_count_ranking_year,high_tech_ent_count_year,high_tech_ent_count_ranking_year,tech_special_ent_count_year,tech_special_ent_count_ranking_year" \
          " FROM ent_tree_info_per_year_stats " \
          "WHERE chain_his_id =%s  AND region_code = '%s' AND year = %s" % (chain_his_id, region_code, datetime_year)
    try:
        res = execute_query(sql)
        return res[0]
    except Exception as e:
        logging.error("get_com_incremental-SQL查询失败：%s" % e)


def get_city_financing(region_code, tree_id):
    """查询城市融资信息
    :param region_code：地区CODE
    :param tree_id：产业链ID
    :return: 城市name+融资金额+融资笔数
    """
    datetime_year = datetime.date.today().year - 1
    chain_his_id = chain_id_transformation(tree_id)
    region_codes = [i[0] for i in get_city_rank_info(region_code)]
    # 按region_code查出城市name+融资金额+融资笔数
    sql = '''SELECT region_code,financing_amount_year,financing_count_year from ent_tree_info_per_year_stats   WHERE region_code in {} and year = %s and chain_his_id =%s '''.format(tuple(region_codes)) % (datetime_year, chain_his_id)
    try:
        res_city_info = execute_query(sql)
    except Exception as e:
        logging.error("get_city_financing--SQL查询失败：%s" % e)
    city_financing_list = []
    for city in res_city_info:
        # 列表内部为tuple格式，转为列表
        city_list = list(city)
        # 把region_code转为name
        city_list[0] = get_city_name(city_list[0])
        # 浮点数转为整数
        city_list[1] = int(city_list[1])
        # 全部添加列表中返回
        city_financing_list.append(city_list)
    return city_financing_list


def get_city_name(region_code):
    """查询城市名称
    :param region_code：地区CODE
    :return: 城市name
    """
    sql = "SELECT name from region_dictionary  WHERE code = '%s'" % region_code
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error("get_city_name--SQL查询失败：%s" % e)


def get_city_leading(region_code, tree_id):
    """查询城市龙头企业数量
    :param region_code：地区CODE
    :param tree_id：产业链ID
    :return: 城市name+企业数量
    """
    datetime_year = datetime.date.today().year - 1
    chain_his_id = chain_id_transformation(tree_id)
    region_codes = [i[0] for i in get_city_rank_info(region_code)]
    sql = '''SELECT region_code,leading_ent_count_year FROM ent_tree_info_per_year_stats WHERE year = %s AND chain_his_id = %s AND region_code in {} '''.format(tuple(region_codes)) % (datetime_year, chain_his_id)
    try:
        res = execute_query(sql)
    except Exception as e:
        logging.error("get_city_leading--SQL查询失败：%s" % e)
    city_financing_list = []
    for city in res:
        # 列表内部为tuple格式，转为列表
        city_list = list(city)
        # 把region_code转为name
        city_list[0] = get_city_name(city_list[0])
        # 浮点数转为整数
        city_list[1] = int(city_list[1])
        # 全部添加列表中返回
        city_financing_list.append(city_list)
    return city_financing_list


